from openpyxl import load_workbook

# 加载 Excel 文件
file_path = 'C:\\Users\\admin\\Desktop\\需要治理的科技贷款数据_0430.xlsx'  # 替换为你的文件路径
workbook = load_workbook(file_path)
sheet = workbook.active  # 获取活动工作表


# 读取第一行作为列名
header_row = next(sheet.iter_rows(min_row=1, max_row=1, values_only=True))
column_names =  [f'"{str(cell)}"' for cell in header_row]   # 将列名转换为字符串

# 生成 CREATE TABLE 语句
create_table_statement = f"CREATE TABLE TEMP_DATA_FZF_FM202505280001 (\n"
create_table_statement += ",\n".join([f"  {col} VARCHAR2(500)" for col in column_names])
create_table_statement += "\n);"

print(create_table_statement)
# 打开一个文本文件用于写入
output_file_path = 'TEMP_DATA_FZF_FM202505280001.sql'  # 输出文件路径
with open(output_file_path, 'w', encoding='utf-8') as output_file:
    # 写入 CREATE TABLE 语句
    output_file.write(create_table_statement + "\n\n")

    # 遍历每一行和每一列
    for row in sheet.iter_rows(min_row=2,values_only=True):
        # 给每列字段拼上双引号并输出
        quoted_row = [f"'{cell}'" if cell is not None else "''" for cell in row]
        # 拼接 SQL 插入语句
        sql_statement = f"INSERT INTO TEMP_DATA_FZF_FM202505280001 VALUES ({', '.join(quoted_row)});\n"
        output_file.write(sql_statement)  # 写入一行并换行